package com.chinatsp.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.jdbc.SQL;

import java.util.List;
import java.util.Map;

/**
 * Created by chenzhengdong on 2017/7/10.
 */
@Mapper
public interface NewsMapper {

    /**
     * 2.3.1 获取在线新闻频道
     *
     * @return
     */
    @Select("SELECT c.channel_id,c.channel_name FROM carpad_news_channel c LIMIT #{pos},#{cnt}")
    List<Map<String, Object>> queryNewsChannel(@Param("pos") String pos, @Param("cnt") String cnt);


    /**
     * 根据id查询新闻详细信息
     *
     * @param news_id
     * @return
     */
    @Select("SELECT cni.news_id,cni.news_title,cni.news_content,cni.news_source,cni.pubdate,cnt.channel_name " +
            "FROM carpad_news_info cni left join carpad_news_title cnt ON cni.news_id=cnt.news_id WHERE cni.news_id = ${news_id}")
    List<Map<String, Object>> getNewsInfo(@Param("news_id") String news_id);

    /**
     * 2.3.2	根据新闻频道ID统计获取新闻数量
     *
     * @param channelID
     * @return
     */
    @Select("SELECT COUNT(*) countnews from carpad_news_title AS t, carpad_news_info AS n WHERE " +
            " t.channel_id = '${channelID}' AND t.news_id = n.news_id AND n.news_content != '' and t.reviewed_status = '1' " +
            "ORDER BY n.pubdate DESC")
    int countNewsListById(@Param("channelID") String channelID);

    /**
     * 2.3.2	获取新闻信息列表
     *
     * @param channelID
     * @param pos
     * @param cnt
     * @return
     */
    @Select("SELECT t.channel_name, t.news_id, t.news_title, t.news_url, t.create_time,n.pubdate," +
            " n.news_source FROM carpad_news_title AS t, carpad_news_info AS n WHERE " +
            " t.channel_id = '${channelID}' AND t.news_id = n.news_id AND n.news_content != '' " +
            " and t.reviewed_status = '1' ORDER BY n.pubdate DESC LIMIT ${pos},${cnt}")
    List<Map<String, Object>> queryNewsList(@Param("channelID") String channelID, @Param
            ("pos") String pos, @Param("cnt") String cnt);

    /**
     * 2.3.2	获取新闻信息列表
     *
     * @param channelID
     * @param pos
     * @param cnt
     * @return
     */
    //使用UserDaoProvider类的findUserById方法来生成sql
    @SelectProvider(type = NewsMapperProvider.class, method = "queryNewsList2")
    List<Map<String, Object>> queryNewsList2(@Param("channelID") String channelID, @Param("pos") int pos, @Param("cnt") int cnt);
    class NewsMapperProvider {
        public String queryNewsList2(){
            return new SQL(){{
                SELECT("t.channel_name,t.news_id,t.news_title,t.news_url,t.create_time");
                SELECT("n.pubdate,n.news_source");
                FROM("carpad_news_title t");
                FROM("carpad_news_info n");
                WHERE("channel_id = #{channelID}");
                WHERE("t.news_id = n.news_id");
                WHERE("n.news_content != ''");
                WHERE("t.reviewed_status = '1'");
                WHERE("t.news_id = n.news_id");
                ORDER_BY("n.pubdate DESC LIMIT #{pos},#{cnt}");
                //从这个toString可以看出，其内部使用高效的StringBuilder实现SQL拼接
            }}.toString();

        }
    }
}
